package servicios.biziGasteiz;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Date;
import java.sql.ResultSet;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.json.JSONArray;
import org.json.JSONException;
import org.json.JSONObject;


import connection.BDConnection;

/**
 * Servlet implementation class HomeGetOutstandingByDate
 */

public class GetEvents extends HttpServlet {
	private static final long serialVersionUID = 1L;
    private String CONTENT_TYPE = "text/json";
    
    /**
     * @see HttpServlet#HttpServlet()
     */
    public GetEvents() {
        super();
        // TODO Auto-generated constructor stub
    }

	/**
	 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		this.processRequest(request, response);
	}

	/**
	 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		this.processRequest(request, response);
	}
	
	private void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		String requestType = (String)request.getParameter("request");
		try{
			if(requestType.compareToIgnoreCase("PLAINDATE") == 0){
				this.plainDate(request, response);
			}
			else if(requestType.compareToIgnoreCase("STARTENDDATE") == 0){
				this.startEndDate(request, response);
			}
			else if(requestType.compareToIgnoreCase("GETEVENT") == 0){
				this.getEvent(request, response);
			}
			else if(requestType.compareToIgnoreCase("GETEVENT2") == 0){
				this.getEvent2(request, response);
			}
			else if(requestType.compareToIgnoreCase("GETNEXTEVENTS") == 0){
				this.getNextEvents(request, response);
			}
			else if(requestType.compareToIgnoreCase("GETMOREEVENTS") == 0){
				this.getMoreEvents(request, response);
			}
			else if(requestType.compareToIgnoreCase("GETSTANDARDTABLE") == 0){
				this.GETSTANDARDTABLE(request, response);
			}
			else if(requestType.compareToIgnoreCase("GETTHEATRESTABLE") == 0){
				this.GETTHEATRESTABLE(request, response);
			}
		}
		catch(Exception oo){oo.printStackTrace();}
	}

	private void GETTHEATRESTABLE(HttpServletRequest request, HttpServletResponse response) throws JSONException, IOException, ClassNotFoundException{
		BDConnection bdCon = new BDConnection();
		try{
			response.setContentType(this.CONTENT_TYPE);
			PrintWriter print = response.getWriter();
			
			String date = request.getParameter("startDate");
			String table = request.getParameter("table");
			String sql = "SELECT DISTINCT cod from sesiones_"+table+" WHERE dia>='"+date+"'";
			ResultSet sesionRs = bdCon.executeQuery(sql);
			JSONArray resJsonArray = new JSONArray();
			while(sesionRs.next()){
				String sqlStr = "SELECT * FROM "+table+" WHERE cod='"+sesionRs.getInt("cod")+"'";
				ResultSet rs = bdCon.executeQuery(sqlStr);
	
				while(rs.next()){
					String cod = rs.getString("cod");
					JSONObject itemJson = new JSONObject();
					itemJson.put("nombre", rs.getString("nombre"));
					itemJson.put("descripccion_corta", rs.getString("descripcion_corta"));
					itemJson.put("descripccion_larga", rs.getString("descripcion_larga"));
					itemJson.put("descripccion_normal", rs.getString("descripcion_normal"));
					
					itemJson.put("precio_anticipado", rs.getDouble("precio_anticipado"));
					itemJson.put("precio_taquilla", rs.getDouble("precio_taquilla"));
					itemJson.put("foto_pequena", rs.getString("foto_pequena"));
					itemJson.put("foto_grande", rs.getString("foto_grande"));
					itemJson.put("cartel", rs.getString("cartel"));
					itemJson.put("destacado", rs.getBoolean("destacado"));
					
					itemJson.put("enlaces", rs.getString("enlaces"));
					itemJson.put("tabla", table);
					itemJson.put("cod", rs.getString("cod"));
					JSONObject lugarJson = new JSONObject();
					String sqlLugar = "SELECT * FROM LUGARES WHERE codigo="+rs.getString("cod_lugar");
					ResultSet rs1 = bdCon.executeQuery(sqlLugar);
					while(rs1.next()){
						lugarJson.put("enlace", rs1.getString("enlace"));
						lugarJson.put("descripcion", rs1.getString("descripcion"));
						lugarJson.put("telefono", rs1.getString("telefono"));
						lugarJson.put("foto", rs1.getString("foto"));
						lugarJson.put("nombre", rs1.getString("nombre"));
						lugarJson.put("calle", rs1.getString("calle"));
					}
					itemJson.put("lugar", lugarJson);
					JSONArray itemsArray = new JSONArray();
					String sqlSesiones = "SELECT * FROM sesiones_"+table+" where cod = "+cod;
					ResultSet rs2 = bdCon.executeQuery(sqlSesiones);
					while(rs2.next()){
						JSONObject item = new JSONObject();
						item.put("hora", rs2.getTime("hora"));
						item.put("dia", rs2.getDate("dia"));
						itemsArray.put(item);
					}
					itemJson.put("sesiones", itemsArray);
					resJsonArray.put(itemJson);
					rs2.close();
					rs1.close();
				}
				rs.close();
			}
			JSONObject resJson = new JSONObject();
			resJson.put("success", true);
			resJson.put("data", resJsonArray);
			print.write(resJson.toString());
			bdCon.closeConnection(sesionRs);
			
		}
		catch(Exception oo){
			JSONObject res = new JSONObject();
			res.put("success", false);
			res.put("data",oo.getMessage());
			oo.printStackTrace();
			bdCon.closeConnection();
			response.getWriter().write(res.toString());
			
		}
	}
	
	
	
	private void GETSTANDARDTABLE(HttpServletRequest request, HttpServletResponse response) throws JSONException, IOException, ClassNotFoundException{
		BDConnection bdCon = new BDConnection();
		try{
			response.setContentType(this.CONTENT_TYPE);
			PrintWriter print = response.getWriter();
			
			String date = request.getParameter("startDate");
			String table = request.getParameter("table");
			
			String sqlStr = "SELECT * FROM "+table+" WHERE fecha>='"+date+"' ORDER BY fecha ASC";
			ResultSet rs = bdCon.executeQuery(sqlStr);
			
			JSONArray resJsonArray = new JSONArray();

			while(rs.next()){
				JSONObject itemJson = new JSONObject();
				itemJson.put("nombre", rs.getString("nombre"));
				itemJson.put("descripccion_corta", rs.getString("descripcion_corta"));
				itemJson.put("descripccion_larga", rs.getString("descripcion_larga"));
				itemJson.put("descripccion_normal", rs.getString("descripcion_normal"));
				itemJson.put("fecha", rs.getDate("fecha").toString());
				itemJson.put("hora", rs.getTime("hora").toString());
				itemJson.put("precio_anticipado", rs.getDouble("precio_anticipado"));
				itemJson.put("precio_taquilla", rs.getDouble("precio_taquilla"));
				itemJson.put("foto_pequena", rs.getString("foto_pequena"));
				itemJson.put("foto_grande", rs.getString("foto_grande"));
				itemJson.put("cartel", rs.getString("cartel"));
				itemJson.put("destacado", rs.getBoolean("destacado"));
				itemJson.put("video", rs.getString("video"));
				itemJson.put("enlaces", rs.getString("enlaces"));
				itemJson.put("tabla", table);
				itemJson.put("cod", rs.getString("cod"));
				try{
					itemJson.put("tipo", rs.getString("tipo"));
				}
				catch(Exception oo){}
				JSONObject lugarJson = new JSONObject();
				String sqlLugar = "SELECT * FROM LUGARES WHERE codigo="+rs.getString("cod_lugar");
				ResultSet rs1 = bdCon.executeQuery(sqlLugar);
				while(rs1.next()){
					lugarJson.put("enlace", rs1.getString("enlace"));
					lugarJson.put("descripcion", rs1.getString("descripcion"));
					lugarJson.put("telefono", rs1.getString("telefono"));
					lugarJson.put("foto", rs1.getString("foto"));
					lugarJson.put("nombre", rs1.getString("nombre"));
					lugarJson.put("calle", rs1.getString("calle"));
				}
				itemJson.put("lugar", lugarJson);
				resJsonArray.put(itemJson);
				rs1.close();
			}
			JSONObject resJson = new JSONObject();
			resJson.put("success", true);
			resJson.put("data", resJsonArray);
			
			print.write(resJson.toString());
			bdCon.closeConnection(rs);
		}
		catch(Exception oo){
			JSONObject res = new JSONObject();
			res.put("success", false);
			res.put("data",oo.getMessage());
			oo.printStackTrace();
			bdCon.closeConnection();
			response.getWriter().write(res.toString());
			
		}
	}
	
	private void getMoreEvents(HttpServletRequest request, HttpServletResponse response) throws JSONException, IOException, ClassNotFoundException{
		BDConnection bdCon = new BDConnection();
		try{
			response.setContentType(this.CONTENT_TYPE);
			PrintWriter print = response.getWriter();
			
			String date = request.getParameter("startDate");
			String table = request.getParameter("table");
			String code = request.getParameter("code");
			
			String sqlStr = "SELECT * FROM "+table+" WHERE cod<>"+code+" AND fecha>='"+date+"' ORDER BY fecha ASC LIMIT 6";
			ResultSet rs = bdCon.executeQuery(sqlStr);
			
			JSONArray resJsonArray = new JSONArray();
			
			while(rs.next()){
				String nombre = rs.getString("nombre");
				Date fecha = rs.getDate("fecha");
				String nombre_foto = rs.getString("foto_pequena");
				String codigo = rs.getString("cod");
				JSONObject itemJson = new JSONObject();
				itemJson.put("nombre", nombre);
				itemJson.put("fecha_inicio", fecha.toString());
				itemJson.put("foto_pequena", nombre_foto);
				itemJson.put("tabla", table);
				itemJson.put("cod", codigo);
				try{
					itemJson.put("tipo", rs.getString("tipo"));
				}
				catch(Exception oo){}
				resJsonArray.put(itemJson);
			}
			JSONObject resJson = new JSONObject();
			resJson.put("success", true);
			resJson.put("data", resJsonArray);
			
			print.write(resJson.toString());
			bdCon.closeConnection(rs);
		}
		catch(Exception oo){
			JSONObject res = new JSONObject();
			res.put("success", false);
			res.put("data",oo.getMessage());
			oo.printStackTrace();
			bdCon.closeConnection();
			response.getWriter().write(res.toString());
			
		}
	}
	
	
	private void getNextEvents(HttpServletRequest request, HttpServletResponse response) throws JSONException, IOException, ClassNotFoundException{
		BDConnection bdCon = new BDConnection();
		try{
			response.setContentType(this.CONTENT_TYPE);
			PrintWriter print = response.getWriter();
			
			String date = request.getParameter("startDate");
			
			
			String sqlStr = "SELECT * FROM relacion_tablas";
			ResultSet rs = bdCon.executeQuery(sqlStr);
			
			JSONArray resJsonArray = new JSONArray();
			
			while(rs.next()){
				String name = rs.getString("nombre_tabla");
				String sql = "SELECT * FROM "+name+" WHERE fecha >= '"+date+"' AND mini_destacado = true ORDER BY fecha ASC LIMIT 4";
				ResultSet rs1 = bdCon.executeQuery(sql);
				while(rs1.next()){
					String nombre = rs1.getString("nombre");
					Date fecha = rs1.getDate("fecha");
					String nombre_foto = rs1.getString("foto_pequena");
					String codigo = rs1.getString("cod");
					JSONObject itemJson = new JSONObject();
					itemJson.put("nombre", nombre);
					itemJson.put("fecha_inicio", fecha.toString());
					itemJson.put("foto_pequena", nombre_foto);
					itemJson.put("tabla", name);
					itemJson.put("cod", codigo);
					try{
						itemJson.put("tipo", rs.getString("tipo"));
					}
					catch(Exception oo){}
					resJsonArray.put(itemJson);
				}
				rs1.close();
			}
			JSONObject resJson = new JSONObject();
			resJson.put("success", true);
			resJson.put("data", resJsonArray);
			
			print.write(resJson.toString());
			bdCon.closeConnection(rs);
		}
		catch(Exception oo){
			JSONObject res = new JSONObject();
			res.put("success", false);
			res.put("data",oo.getMessage());
			oo.printStackTrace();
			bdCon.closeConnection();
			response.getWriter().write(res.toString());
			
		}
	}
	
	
	private void getEvent2(HttpServletRequest request, HttpServletResponse response) throws JSONException, IOException, ClassNotFoundException{
		BDConnection bdCon = new BDConnection();
		try{
			response.setContentType(this.CONTENT_TYPE);
			PrintWriter print = response.getWriter();
			
			String table = request.getParameter("table");
			String cod = request.getParameter("code");
			
			
			String sqlStr = "SELECT * FROM "+table+" WHERE cod="+cod;
			ResultSet rs = bdCon.executeQuery(sqlStr);
			
			JSONArray resJsonArray = new JSONArray();
			
			while(rs.next()){
				JSONObject itemJson = new JSONObject();
				itemJson.put("nombre", rs.getString("nombre"));
				itemJson.put("descripccion_corta", rs.getString("descripcion_corta"));
				itemJson.put("descripccion_larga", rs.getString("descripcion_larga"));
				itemJson.put("descripccion_normal", rs.getString("descripcion_normal"));
				itemJson.put("precio_anticipado", rs.getDouble("precio_anticipado"));
				itemJson.put("precio_taquilla", rs.getDouble("precio_taquilla"));
				itemJson.put("foto_pequena", rs.getString("foto_pequena"));
				itemJson.put("foto_grande", rs.getString("foto_grande"));
				itemJson.put("cartel", rs.getString("cartel"));
				itemJson.put("destacado", rs.getBoolean("destacado"));
				itemJson.put("enlaces", rs.getString("enlaces"));
				
				JSONObject lugarJson = new JSONObject();
				String sqlLugar = "SELECT * FROM LUGARES WHERE codigo="+rs.getString("cod_lugar");
				ResultSet rs1 = bdCon.executeQuery(sqlLugar);
				while(rs1.next()){
					lugarJson.put("enlace", rs1.getString("enlace"));
					lugarJson.put("descripcion", rs1.getString("descripcion"));
					lugarJson.put("telefono", rs1.getString("telefono"));
					lugarJson.put("foto", rs1.getString("foto"));
					lugarJson.put("nombre", rs1.getString("nombre"));
					lugarJson.put("calle", rs1.getString("calle"));
				}
				itemJson.put("lugar", lugarJson);
				resJsonArray.put(itemJson);
				JSONArray itemsArray = new JSONArray();
				String sqlSesiones = "SELECT * FROM sesiones_"+table+" where cod = "+cod;
				ResultSet rs2 = bdCon.executeQuery(sqlSesiones);
				while(rs2.next()){
					JSONObject item = new JSONObject();
					item.put("hora", rs2.getTime("hora"));
					item.put("dia", rs2.getDate("dia"));
					itemsArray.put(item);
				}
				itemJson.put("sesiones", itemsArray);
				rs1.close();
				rs2.close();
			}
			JSONObject resJson = new JSONObject();
			resJson.put("success", true);
			resJson.put("data", resJsonArray);
			
			bdCon.closeConnection(rs);
			print.write(resJson.toString());
		}
		catch(Exception oo){
			JSONObject res = new JSONObject();
			res.put("success", false);
			res.put("data",oo.getMessage());
			
			bdCon.closeConnection();
			response.getWriter().write(res.toString());
			oo.printStackTrace();
		}
	}
	
	private void getEvent(HttpServletRequest request, HttpServletResponse response) throws JSONException, IOException, ClassNotFoundException{
		BDConnection bdCon = new BDConnection();
		try{
			response.setContentType(this.CONTENT_TYPE);
			PrintWriter print = response.getWriter();
			
			String table = request.getParameter("table");
			String cod = request.getParameter("code");
			
			
			String sqlStr = "SELECT * FROM "+table+" WHERE cod="+cod;
			ResultSet rs = bdCon.executeQuery(sqlStr);
			
			JSONArray resJsonArray = new JSONArray();
			
			while(rs.next()){
				JSONObject itemJson = new JSONObject();
				itemJson.put("nombre", rs.getString("nombre"));
				itemJson.put("descripccion_corta", rs.getString("descripcion_corta"));
				itemJson.put("descripccion_larga", rs.getString("descripcion_larga"));
				itemJson.put("descripccion_normal", rs.getString("descripcion_normal"));
				itemJson.put("fecha", rs.getDate("fecha").toString());
				itemJson.put("hora", rs.getTime("hora").toString());
				itemJson.put("precio_anticipado", rs.getDouble("precio_anticipado"));
				itemJson.put("precio_taquilla", rs.getDouble("precio_taquilla"));
				itemJson.put("foto_pequena", rs.getString("foto_pequena"));
				itemJson.put("foto_grande", rs.getString("foto_grande"));
				itemJson.put("cartel", rs.getString("cartel"));
				itemJson.put("destacado", rs.getBoolean("destacado"));
				itemJson.put("video", rs.getString("video"));
				itemJson.put("enlaces", rs.getString("enlaces"));
				try{
					itemJson.put("tipo", rs.getString("tipo"));
				}
				catch(Exception oo){}
				JSONObject lugarJson = new JSONObject();
				String sqlLugar = "SELECT enlace,descripcion,telefono,foto,nombre,calle," +
						"ST_ASTEXT(the_geom) as localizacion FROM LUGARES WHERE codigo="+rs.getString("cod_lugar");
				ResultSet rs1 = bdCon.executeQuery(sqlLugar);
				while(rs1.next()){
					lugarJson.put("enlace", rs1.getString("enlace"));
					lugarJson.put("descripcion", rs1.getString("descripcion"));
					lugarJson.put("telefono", rs1.getString("telefono"));
					lugarJson.put("foto", rs1.getString("foto"));
					lugarJson.put("nombre", rs1.getString("nombre"));
					lugarJson.put("calle", rs1.getString("calle"));
					lugarJson.put("localizacion", rs1.getString("localizacion"));
				}
				itemJson.put("lugar", lugarJson);
				resJsonArray.put(itemJson);
				rs1.close();
			}
			JSONObject resJson = new JSONObject();
			resJson.put("success", true);
			resJson.put("data", resJsonArray);
			
			bdCon.closeConnection(rs);
			print.write(resJson.toString());
		}
		catch(Exception oo){
			JSONObject res = new JSONObject();
			res.put("success", false);
			res.put("data",oo.getMessage());
			
			bdCon.closeConnection();
			response.getWriter().write(res.toString());
			oo.printStackTrace();
		}
	}
	
	private void plainDate(HttpServletRequest request, HttpServletResponse response) throws JSONException, IOException, ClassNotFoundException{
		BDConnection bdCon = new BDConnection();
		try{
			response.setContentType(this.CONTENT_TYPE);
			PrintWriter print = response.getWriter();
			
			String date = request.getParameter("date");
			
			
			String sqlStr = "SELECT * FROM relacion_tablas";
			ResultSet rs = bdCon.executeQuery(sqlStr);
			
			JSONArray resJsonArray = new JSONArray();
			
			while(rs.next()){
				String name = rs.getString("nombre_tabla");
				String sql = "SELECT * FROM "+name+" WHERE fecha = '"+date+"'";
				ResultSet rs1 = bdCon.executeQuery(sql);
				while(rs1.next()){
					JSONObject itemJson = new JSONObject();
					itemJson.put("nombre", rs1.getString("nombre"));
					itemJson.put("descripccion_corta", rs1.getString("descripcion_corta"));
					itemJson.put("descripccion_larga", rs1.getString("descripcion_larga"));
					itemJson.put("descripccion_normal", rs1.getString("descripcion_normal"));
					itemJson.put("fecha", rs1.getDate("fecha").toString());
					itemJson.put("hora", rs1.getTime("hora").toString());
					itemJson.put("precio_anticipado", rs1.getDouble("precio_anticipado"));
					itemJson.put("precio_taquilla", rs1.getDouble("precio_taquilla"));
					itemJson.put("foto_pequena", rs1.getString("foto_pequena"));
					itemJson.put("foto_grande", rs1.getString("foto_grande"));
					itemJson.put("cartel", rs1.getString("cartel"));
					itemJson.put("destacado", rs1.getBoolean("destacado"));
					itemJson.put("video", rs1.getString("video"));
					itemJson.put("enlaces", rs1.getString("enlaces"));
					itemJson.put("tabla", name);try{
						itemJson.put("tipo", rs.getString("tipo"));
					}
					catch(Exception oo){}
					JSONObject lugarJson = new JSONObject();
					String sqlLugar = "SELECT * FROM LUGARES WHERE codigo="+rs1.getString("cod_lugar");
					ResultSet rs2 = bdCon.executeQuery(sqlLugar);
					while(rs2.next()){
						lugarJson.put("enlace", rs2.getString("enlace"));
						lugarJson.put("descripcion", rs2.getString("descripcion"));
						lugarJson.put("telefono", rs2.getString("telefono"));
						lugarJson.put("foto", rs2.getString("foto"));
						lugarJson.put("nombre", rs2.getString("nombre"));
						lugarJson.put("calle", rs2.getString("calle"));
					}
					itemJson.put("lugar", lugarJson);
					resJsonArray.put(itemJson);
				}
			}
			JSONObject resJson = new JSONObject();
			resJson.put("success", true);
			resJson.put("data", resJsonArray);
			
			bdCon.closeConnection();
			print.write(resJson.toString());
		}
		catch(Exception oo){
			JSONObject res = new JSONObject();
			res.put("success", false);
			res.put("data",oo.getMessage());
			
			bdCon.closeConnection();
			response.getWriter().write(res.toString());
			
		}
	}
	
	private void startEndDate(HttpServletRequest request, HttpServletResponse response) throws JSONException, IOException, ClassNotFoundException{
		BDConnection bdCon = new BDConnection();
		try{
			response.setContentType(this.CONTENT_TYPE);
			PrintWriter print = response.getWriter();
			
			String startDate = request.getParameter("startDate");
			String endDate = request.getParameter("endDate");
			
			
			String sqlStr = "SELECT * FROM relacion_tablas";
			ResultSet rs = bdCon.executeQuery(sqlStr);
			
			JSONArray resJsonArray = new JSONArray();
			
			while(rs.next()){
				String name = rs.getString("nombre_tabla");
				String sql = "SELECT * FROM "+name+" WHERE fecha >= '"+startDate+"' AND fecha <= '"+endDate+"' AND destacado = true";
				ResultSet rs1 = bdCon.executeQuery(sql);
				while(rs1.next()){
					String nombre = rs1.getString("nombre");
					String descripcion = rs1.getString("descripcion_corta");
					Date fecha = rs1.getDate("fecha");
					//String lugar = rs1.getString("lugar");
					double precio = rs1.getDouble("precio_taquilla");
					String foto_grande = rs1.getString("foto_grande");
					String foto_pequena = rs1.getString("foto_pequena");
					boolean destacado = rs1.getBoolean("destacado");
					int codigo = rs1.getInt("cod");
					JSONObject itemJson = new JSONObject();
					itemJson.put("nombre", nombre);
					itemJson.put("descripccion", descripcion);
					itemJson.put("fecha_inicio", fecha.toString());
					itemJson.put("precio", precio);
					itemJson.put("foto_pequena", foto_pequena);
					itemJson.put("foto_grande", foto_grande);
					itemJson.put("destacado", destacado);
					itemJson.put("table_name", name);
					itemJson.put("cod", codigo);
					try{
						itemJson.put("tipo", rs.getString("tipo"));
					}
					catch(Exception oo){}
					resJsonArray.put(itemJson);
				}
			}
			JSONObject resJson = new JSONObject();
			resJson.put("success", true);
			resJson.put("data", resJsonArray);
			
			bdCon.closeConnection(rs);
			print.write(resJson.toString());
		}
		catch(Exception oo){
			JSONObject res = new JSONObject();
			res.put("success", false);
			res.put("data",oo.getMessage());
			bdCon.closeConnection();
			response.getWriter().write(res.toString());
		}
	}
	
}
